USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetFinalizedSourcesByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetFinalizedSourcesByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetFinalizedSourcesByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @FiltredCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @FiltredCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)
	

	DECLARE @transaction_isolation_level SMALLINT;
	
	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;
	
	print('spGetFinalizedSourcesByCompanyIds transaction_isolation_level: ' + STR(@transaction_isolation_level))


	declare @selectedCompanies table 
	(
		Id uniqueidentifier, 
		FinalVersion int
	)
	
	insert @selectedCompanies select CompanyId, WorkingVersion - 1 
	from Companies 
	INNER JOIN @FiltredCompanies AS FiltredCompanies ON FiltredCompanies.Id = Companies.CompanyId
	where WorkingVersion > 1002 and IsParked = 0 and IsControversyResearchOnly = 0 
	--and (LastFinalUpdate > @ChangedSince or LastCentralizedResearchUpdate > @ChangedSince)


	declare @sources table 
	(
		ID int identity(1,1) NOT NULL primary key,
		CompanyId uniqueidentifier, 
		SourceId uniqueidentifier, 
		SourceName nvarchar(max),
		SourceText nvarchar(max),
		IndicatorNumber nvarchar(max)
	)
	insert into @sources

	select s.CompanyId, s.SourceId, s.Name As SourceName, CONVERT(nvarchar(max), s.Text) SourceText, i.Number as IndicatorNumber 
	from Sources s
	join @selectedCompanies sc on sc.Id = s.CompanyId
	join IndicatorSources indS on s.SourceId = inds.SourceId
	join Indicators i on inds.IndicatorId = i.IndicatorId 
	and i.IndicatorTypeId = 1 and i.Version = sc.FinalVersion and LEN(i.Number) > 3
	union
	select s.CompanyId, s.SourceId, s.Name as SourceName, CONVERT(nvarchar(max), s.Text) SourceText, i.Number as IndicatorNumber 
	from Sources s
	join @selectedCompanies sc on sc.Id = s.CompanyId
	join CommentsAlphanumericSources canS on s.SourceId = cans.SourceId
	join CommentsAlphanumeric can on cans.CommentsAlphanumericId = can.CommentsAlphanumericId
	join Indicators i on can.IndicatorId = i.IndicatorId 
	and i.IndicatorTypeId = 1 and i.Version = sc.FinalVersion and LEN(i.Number) > 3
	order by CompanyId, SourceId


	declare @sourcesWithIndicators table 
	(
		CompanyId uniqueidentifier, 
		SourceId uniqueidentifier, 
		SourceName nvarchar(max),
		SourceText nvarchar(max),
		IndicatorsNumbers nvarchar(max)
	)

	DECLARE @companyId uniqueidentifier
	DECLARE @sourceId uniqueidentifier
	DECLARE @sourceName nvarchar(Max)
	DECLARE @sourceText nvarchar(Max)
	DECLARE @indicatorNumber nvarchar(Max)
	DECLARE @indicators nvarchar(Max)


	declare @ID int
	declare @Count int
	declare @currentSourceId uniqueidentifier
	declare @lastSourceId uniqueidentifier

	select @ID = min(ID) from @sources
	select @Count = max(ID) from @sources
	select @currentSourceId = SourceId from @sources where ID = @ID
    set @indicators = ''

	while @ID <= @Count begin
		select @sourceId = SourceId	from @sources where ID = @ID

		if (@sourceId != @currentSourceId)
		begin
			set @currentSourceId = @sourceId

			insert into @sourcesWithIndicators
		    values (@companyId, @lastSourceId, @sourceName, @sourceText, @indicators)
			
			set @indicators = ''
		end
		
		select @companyId = CompanyId, 
		       @lastSourceId = SourceId,
			   @sourceName = SourceName,
			   @sourceText = SourceText,
			   @indicatorNumber = IndicatorNumber
		from @sources 
		where ID = @ID

		if @indicators = ''
			set @indicators = @indicatorNumber
		else
			set @indicators = @indicators + ', ' + @indicatorNumber

		set @ID = @ID + 1
	end

	SELECT	SourceId,
	        CompanyId,
			SourceName,
			SourceText,
			IndicatorsNumbers
	FROM @sourcesWithIndicators
	WHERE SourceName<>'.1'
	ORDER BY CompanyId, SourceName

END
GO


